﻿-- =============================================
-- Script Template
-- =============================================

CREATE PROCEDURE P_TrendChart_SSQ_HMFB --双色球号码分布图

AS

set nocount on

Create table #LotteryNumberListSSQ(id int identity(1,1),Isuse varchar(20),LotteryNumber varchar(100),
		B_1 int,B_2 int,B_3 int,B_4 int,B_5 int,B_6 int,B_7 int,B_8 int,B_9 int,B_10 int,B_11 int,
		B_12 int,B_13 int,B_14 int,B_15 int,B_16 int,B_17 int,B_18 int,B_19 int,B_20 int,B_21 int,
		B_22 int,B_23 int,B_24 int,B_25 int,B_26 int,B_27 int,B_28 int,B_29 int,B_30 int,B_31 int,	
		B_32 int,B_33 int,BQ_0 int,K_0 int,L_012 int,C_H int,L_H int,Z_H int,S_Q varchar(20),J_O varchar(20))

declare @cur cursor 
declare @Isuse varchar(20), @WinLotteryNumber varchar(100), @id int 

declare @B_1 int,@B_2 int,@B_3 int,@B_4 int,@B_5 int,@B_6 int,@B_7 int,@B_8 int,@B_9 int,@B_10 int,
		@B_11 int,@B_12 int,@B_13 int,@B_14 int,@B_15 int,@B_16 int,@B_17 int,@B_18 int,@B_19 int,@B_20 int,
		@B_21 int,@B_22 int,@B_23 int,@B_24 int,@B_25 int,@B_26 int,@B_27 int,@B_28 int,@B_29 int,@B_30 int,
		@B_31 int,@B_32 int,@B_33 int,@BQ_0 int,@K_0 int,@L_012 int,@C_H int,@L_H int,@Z_H int,@Q_1 INT,@Q_2 int,
		@Q_3 int,@J int,@O int,@S_Q varchar(20), @J_O varchar(20)

DECLARE @C_H_1 INT,@C_H_2 INT,@C_H_3 INT,@C_H_4 INT,@C_H_5 INT,@C_H_6 INT
DECLARE @QW int,@BW int,@SW int,@W int,@Q INT,@B INT,@S INT,@G INT



Create table #tb(id int,PlayNo varchar(20),WinLotteryNumber varchar(100))

		insert into #tb select Id,PlayNo, WinLotteryNumber from pm_plays  with(nolock) where WinLotteryNumber <> '' and LotteryID = 5 order by [EndTime] asc

set @B_1 = 0 set @B_2 = 0 set @B_3 = 0 set @B_4 = 0 set @B_5 = 0 set @B_6 = 0  set @B_7 = 0 set @B_8 = 0 set @B_9 = 0
set @B_10 = 0 set @B_11 = 0 set @B_12 = 0 set @B_13 = 0 set @B_14 = 0 set @B_15 = 0 set @B_16 = 0  set @B_17 = 0 set @B_18 = 0 
set @B_19 = 0 set @B_20 = 0 set @B_21 = 0 set @B_22 = 0 set @B_23 = 0 set @B_24 = 0 set @B_25 = 0 set @B_26 = 0  set @B_27 = 0 
set @B_28 = 0 set @B_29 = 0 set @B_30 = 0 set @B_31 = 0 set @B_32 = 0 set @B_33 = 0 

SET @C_H_1 = 0 SET @C_H_2 = 0 SET @C_H_3 = 0 SET @C_H_4 = 0 SET @C_H_5 = 0 SET @C_H_6 = 0 

set @cur = cursor FAST_FORWARD for select PlayNo,WinLotteryNumber from #tb 

open @cur

fetch next from @cur into @Isuse, @WinLotteryNumber

while @@fetch_status=0
begin

set @QW = Substring(@WinLotteryNumber,1,2) 
set @BW = Substring(@WinLotteryNumber,4,2) 
set @SW = Substring(@WinLotteryNumber,7,2)
set @W = Substring(@WinLotteryNumber,10,2) 
set @Q = Substring(@WinLotteryNumber,13,2)
set @B = Substring(@WinLotteryNumber,16,2) 
set @S = Substring(@WinLotteryNumber,21,2)
set @G = Substring(@WinLotteryNumber,24,2)

SET @J=0 SET @O = 0 SET @Q_1=0 SET @Q_2=0 SET @Q_3=0 SET @L_012=0 SET @L_H = 0 SET @Z_H=0 SET @C_H=0

set @B_1 =@B_1 + 1 set @B_2 = @B_2 + 1 set @B_3 = @B_3 + 1 set @B_4 = @B_4 + 1 set @B_5 = @B_5 + 1 set @B_6 = @B_6 + 1
set @B_7 = @B_7 + 1 set @B_8 = @B_8 + 1 set @B_9 = @B_9 + 1 set @B_10 = @B_10 + 1 set @B_11 =@B_11 + 1 set @B_12 = @B_12 + 1 
set @B_13 = @B_13 + 1 set @B_14 = @B_14 + 1 set @B_15 = @B_15 + 1 set @B_16 = @B_16 + 1  set @B_17 = @B_17 + 1 set @B_18 = @B_18 + 1
set @B_19 = @B_19 + 1 set @B_20 = @B_20 + 1 set @B_21 =@B_21 + 1 set @B_22 = @B_22 + 1  set @B_23 = @B_23 + 1 set @B_24 = @B_24 + 1 
set @B_25 = @B_25 + 1 set @B_26 = @B_26 + 1  set @B_27 = @B_27 + 1 set @B_28 = @B_28 + 1 set @B_29 = @B_29 + 1 set @B_30 = @B_30 + 1 
set @B_31 =@B_31 + 1 set @B_32 = @B_32 + 1  set @B_33 = @B_33 + 1

IF(@QW=1 OR @BW = 1 OR @SW=1 OR @W=1 OR @Q=1 OR @B=1)
BEGIN
	SET @B_1 = 0
END

IF(@QW=2 OR @BW = 2 OR @SW=2 OR @W=2 OR @Q=2 OR @B=2)
BEGIN
	SET @B_2 = 0
END

IF(@QW=3 OR @BW = 3 OR @SW=3 OR @W=3 OR @Q=3 OR @B=3)
BEGIN
	SET @B_3 = 0
END

IF(@QW=4 OR @BW = 4 OR @SW=4 OR @W=4 OR @Q=4 OR @B=4)
BEGIN
	SET @B_4 = 0
END

IF(@QW=5 OR @BW = 5 OR @SW=5 OR @W=5 OR @Q=5 OR @B=5)
BEGIN
	SET @B_5 = 0
END

IF(@QW=6 OR @BW = 6 OR @SW=6 OR @W=6 OR @Q=6 OR @B=6)
BEGIN
	SET @B_6 = 0
END

IF(@QW=7 OR @BW = 7 OR @SW=7 OR @W=7 OR @Q=7 OR @B=7)
BEGIN
	SET @B_7 = 0
END

IF(@QW=8 OR @BW = 8 OR @SW=8 OR @W=8 OR @Q=8 OR @B=8)
BEGIN
	SET @B_8 = 0
END

IF(@QW=9 OR @BW = 9 OR @SW=9 OR @W=9 OR @Q=9 OR @B=9)
BEGIN
	SET @B_9 = 0
END

IF(@QW=10 OR @BW = 10 OR @SW=10 OR @W=10 OR @Q=10 OR @B=10)
BEGIN
	SET @B_10 = 0
END

IF(@QW=11 OR @BW = 11 OR @SW=11 OR @W=11 OR @Q=11 OR @B=11)
BEGIN
	SET @B_11 = 0
END

IF(@QW=12 OR @BW = 12 OR @SW=12 OR @W=12 OR @Q=12 OR @B=12)
BEGIN
	SET @B_12 = 0
END

IF(@QW=13 OR @BW = 13 OR @SW=13 OR @W=13 OR @Q=13 OR @B=13)
BEGIN
	SET @B_13 = 0
END

IF(@QW=14 OR @BW = 14 OR @SW=14 OR @W=14 OR @Q=14 OR @B=14)
BEGIN
	SET @B_14 = 0
END

IF(@QW=15 OR @BW = 15 OR @SW=15 OR @W=15 OR @Q=15 OR @B=15)
BEGIN
	SET @B_15 = 0
END

IF(@QW=16 OR @BW = 16 OR @SW=16 OR @W=16 OR @Q=16 OR @B=16)
BEGIN
	SET @B_16 = 0
END

IF(@QW=17 OR @BW = 17 OR @SW=17 OR @W=17 OR @Q=17 OR @B=17)
BEGIN
	set @B_17 = 0
END

IF(@QW=18 OR @BW = 18 OR @SW=18 OR @W=18 OR @Q=18 OR @B=18)
BEGIN
	SET @B_18 = 0
END

IF(@QW=19 OR @BW = 19 OR @SW=19 OR @W=19 OR @Q=19 OR @B=19)
BEGIN
	SET @B_19 = 0
END

IF(@QW=20 OR @BW = 20 OR @SW=20 OR @W=20 OR @Q=20 OR @B=20)
BEGIN
	SET @B_20 = 0
END

IF(@QW=21 OR @BW = 21 OR @SW=21 OR @W=21 OR @Q=21 OR @B=21)
BEGIN
	SET @B_21 = 0
END

IF(@QW=22 OR @BW = 22 OR @SW=22 OR @W=22 OR @Q=22 OR @B=22)
BEGIN
	SET @B_22 = 0
END

IF(@QW=23 OR @BW = 23 OR @SW=23 OR @W=23 OR @Q=23 OR @B=23)
BEGIN
	SET @B_23 = 0
END

IF(@QW=24 OR @BW = 24 OR @SW=24 OR @W=24 OR @Q=24 OR @B=24)
BEGIN
	SET @B_24 = 0
END

IF(@QW=25 OR @BW = 25 OR @SW=25 OR @W=25 OR @Q=25 OR @B=25)
BEGIN
	SET @B_25 = 0
END

IF(@QW=26 OR @BW = 26 OR @SW=26 OR @W=26 OR @Q=26 OR @B=26)
BEGIN
	SET @B_26 = 0
END

IF(@QW=27 OR @BW = 27 OR @SW=27 OR @W=27 OR @Q=27 OR @B=27)
BEGIN
	SET @B_27 = 0
END

IF(@QW=28 OR @BW = 28 OR @SW=28 OR @W=28 OR @Q=28 OR @B=28)
BEGIN
	SET @B_28 = 0
END

IF(@QW=29 OR @BW = 29 OR @SW=29 OR @W=29 OR @Q=29 OR @B=29)
BEGIN
	SET @B_29 = 0
END

IF(@QW=30 OR @BW = 30 OR @SW=30 OR @W=30 OR @Q=30 OR @B=30)
BEGIN
	SET @B_30 = 0
END

IF(@QW=31 OR @BW = 31 OR @SW=31 OR @W=31 OR @Q=31 OR @B=31)
BEGIN
	SET @B_31 = 0
END

IF(@QW=32 OR @BW = 32 OR @SW=32 OR @W=32 OR @Q=32 OR @B=32)
BEGIN
	SET @B_32 = 0
END

IF(@QW=33 OR @BW = 33 OR @SW=33 OR @W=33 OR @Q=33 OR @B=33)
BEGIN
	SET @B_33 = 0
END

SET @BQ_0 = @S
set @K_0 = @G
SET @L_012 = @S % 3

IF(@QW+1<>@BW and @BW+1<>@SW AND @SW+1<>@W AND @W+1<>@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 0		
END

IF(@QW+1=@BW and @BW+1<>@SW AND @SW+1<>@W AND @W+1<>@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 2			
END

IF(@QW+1<>@BW and @BW+1=@SW AND @SW+1<>@W AND @W+1<>@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 2			
END

IF(@QW+1<>@BW and @BW+1<>@SW AND @SW+1=@W AND @W+1<>@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 2			
END

IF(@QW+1<>@BW and @BW+1<>@SW AND @SW+1<>@W AND @W+1=@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 2			
END

IF(@QW+1<>@BW and @BW+1<>@SW AND @SW+1<>@W AND @W+1<>@Q AND @Q+1=@B)
BEGIN
	set @L_H = 2			
END

IF(@QW+1=@BW and @BW+1=@SW AND @SW+1<>@W AND @W+1<>@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 3			
END

IF(@QW+1<>@BW and @BW+1=@SW AND @SW+1=@W AND @W+1<>@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 3			
END

IF(@QW+1<>@BW and @BW+1<>@SW AND @SW+1=@W AND @W+1=@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 3			
END

IF(@QW+1<>@BW and @BW+1<>@SW AND @SW+1<>@W AND @W+1=@Q AND @Q+1=@B)
BEGIN
	set @L_H = 3			
END

IF(@QW+1=@BW  AND @SW+1=@W AND @W+1<>@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 4			
END

IF(@QW+1<>@BW and @BW+1=@SW AND @W+1=@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 4			
END

IF(@QW+1<>@BW and @BW+1<>@SW AND @SW+1=@W AND @Q+1=@B)
BEGIN
	set @L_H = 4			
END

IF(@QW+1=@BW and @BW+1<>@SW AND @SW+1<>@W AND @W+1=@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 4			
END

IF(@QW+1=@BW and @BW+1<>@SW AND @SW+1<>@W AND @W+1<>@Q AND @Q+1=@B)
BEGIN
	set @L_H = 4			
END

IF(@QW+1<>@BW and @BW+1=@SW AND @SW+1<>@W AND @W+1=@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 4			
END

IF(@QW+1<>@BW and @BW+1=@SW AND @SW+1<>@W AND @W+1<>@Q AND @Q+1=@B)
BEGIN
	set @L_H = 4			
END

IF(@QW+1=@BW AND @SW+1=@W AND @W+1=@Q AND @Q+1<>@B)
BEGIN
	set @L_H = 5			
END

IF(@QW+1<>@BW and @BW+1=@SW AND @W+1=@Q AND @Q+1=@B)
BEGIN
	set @L_H = 5		
END

IF(@QW+1=@BW and @BW+1<>@SW AND @SW+1<>@W AND @W+1=@Q AND @Q+1=@B)
BEGIN
	set @L_H = 5			
END

IF(@QW+1=@BW  AND @SW+1=@W AND @W+1=@Q AND @Q+1=@B)
BEGIN
	set @L_H = 6			
END

IF(@QW+1=@BW and @BW+1=@SW  AND @W+1=@Q AND @Q+1=@B)
BEGIN
	set @L_H = 6			
END

IF(@QW+1=@BW and @BW+1=@SW AND @SW+1=@W AND @Q+1=@B)
BEGIN
	set @L_H = 6			
END

set @Z_H = @QW + @BW + @SW + @W + @Q + @B

IF(@QW < 12 AND 0<@QW)
BEGIN
	SET @Q_1 =@Q_1 + 1
END

IF(@BW < 12)
BEGIN
	SET @Q_1 =@Q_1 + 1
END

IF(@SW < 12)
BEGIN
	SET @Q_1 =@Q_1 + 1
END

IF(@W < 12)
BEGIN
	SET @Q_1 =@Q_1 + 1
END

IF(@Q < 12)
BEGIN
	SET @Q_1 =@Q_1 + 1
END

IF(@B < 12)
BEGIN
	SET @Q_1 =@Q_1 + 1
END

IF(11 < @QW AND @QW < 23)
BEGIN
	SET @Q_2 =@Q_2 + 1
END

IF(11 < @BW AND @BW < 23)
BEGIN
	SET @Q_2 =@Q_2 + 1
END

IF(11 < @SW AND @SW < 23)
BEGIN
	SET @Q_2 =@Q_2 + 1
END

IF(11 < @W AND @W < 23)
BEGIN
	SET @Q_2 =@Q_2 + 1
END

IF(11 < @Q AND @Q < 23)
BEGIN
	SET @Q_2 =@Q_2 + 1
END

IF(11 < @B AND @B < 23)
BEGIN
	SET @Q_2 =@Q_2 + 1
END

IF(22 < @QW AND @QW < 34)
BEGIN
	SET @Q_3 =@Q_3 + 1
END

IF(22 < @BW AND @BW < 34)
BEGIN
	SET @Q_3 =@Q_3 + 1
END

IF(22 < @SW AND @SW < 34)
BEGIN
	SET @Q_3 =@Q_3 + 1
END

IF(22 < @W AND @W < 34)
BEGIN
	SET @Q_3 =@Q_3 + 1
END

IF(22 < @Q AND @Q < 34)
BEGIN
	SET @Q_3 =@Q_3 + 1
END

IF(22 < @B AND @B < 34)
BEGIN
	SET @Q_3 =@Q_3 + 1
END

SET @S_Q =(rtrim(ltrim(str(@Q_1))) +':'+rtrim(ltrim(str(@Q_2))) +':'+rtrim(ltrim(str(@Q_3))))

-------基偶统计

IF(@QW % 2 = 0)
BEGIN
	SET @O =@O + 1
END

IF(@BW % 2 = 0)
BEGIN
	SET @O =@O + 1
END

IF(@SW % 2 = 0)
BEGIN
	SET @O =@O + 1
END

IF(@W % 2 = 0)
BEGIN
	SET @O =@O + 1
END

IF(@Q % 2 = 0)
BEGIN
	SET @O =@O + 1
END

IF(@B % 2 = 0)
BEGIN
	SET @O =@O + 1
END

IF(@QW % 2 <> 0)
BEGIN
	SET @J =@J + 1
END

IF(@BW % 2 <> 0)
BEGIN
	SET @J =@J + 1
END

IF(@SW % 2 <> 0)
BEGIN
	SET @J =@J + 1
END

IF(@W % 2 <> 0)
BEGIN
	SET @J =@J + 1
END

IF(@Q % 2 <> 0)
BEGIN
	SET @J =@J + 1
END

IF(@B % 2 <> 0)
BEGIN
	SET @J =@J + 1
END

SET @J_O = (rtrim(ltrim(STR(@J)))) +' : '+(rtrim(ltrim(STR(@O))))

-------计算重号
if(@C_H_1 = @QW OR @C_H_1 =@BW OR @C_H_1=@SW OR @C_H_1=@W OR @C_H_1=@Q OR @C_H_1=@B)
BEGIN
	SET @C_H =@C_H + 1
END

if(@C_H_2 = @QW OR @C_H_2 =@BW OR @C_H_2=@SW OR @C_H_2=@W OR @C_H_2=@Q OR @C_H_2=@B)
BEGIN
	SET @C_H =@C_H + 1
END

if(@C_H_3 = @QW OR @C_H_3 =@BW OR @C_H_3=@SW OR @C_H_3=@W OR @C_H_3=@Q OR @C_H_3=@B)
BEGIN
	SET @C_H =@C_H + 1
END

if(@C_H_4 = @QW OR @C_H_4 =@BW OR @C_H_4=@SW OR @C_H_4=@W OR @C_H_4=@Q OR @C_H_4=@B)
BEGIN
	SET @C_H =@C_H + 1
END

if(@C_H_5 = @QW OR @C_H_5 =@BW OR @C_H_5=@SW OR @C_H_5=@W OR @C_H_5=@Q OR @C_H_5=@B)
BEGIN
	SET @C_H =@C_H + 1
END

if(@C_H_6 = @QW OR @C_H_6 =@BW OR @C_H_6=@SW OR @C_H_6=@W OR @C_H_6=@Q OR @C_H_6=@B)
BEGIN
	SET @C_H =@C_H + 1
END

SET @C_H_1 = @QW SET @C_H_2 = @BW SET @C_H_3 = @SW SET @C_H_4 = @W SET @C_H_5 = @Q SET @C_H_6 = @B

insert into #LotteryNumberListSSQ values (@Isuse, @WinLotteryNumber ,@B_1,@B_2 ,@B_3 ,@B_4 ,@B_5,@B_6 ,
		@B_7 ,@B_8,@B_9,@B_10,@B_11,@B_12 ,@B_13 ,@B_14 ,@B_15,@B_16 ,@B_17 ,@B_18,@B_19,@B_20,@B_21,@B_22, 
		@B_23 ,@B_24 ,@B_25,@B_26 ,@B_27 ,@B_28,@B_29,@B_30,@B_31,@B_32, @B_33, @BQ_0,@K_0,@L_012,@C_H,@L_H,
		@Z_H,@S_Q,@J_O)

fetch next from @cur into @Isuse, @WinLotteryNumber

end

close @cur

select * from #LotteryNumberListSSQ

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
